from __future__ import annotations
from typing import Tuple
from numpy import ndarray, array
from pandas import Series
from pandas.core.frame import DataFrame
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle
from copy import copy, deepcopy
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.worksheet.page import (
    PageMargins, 
    PrintOptions,
    PrintPageSetup,   
)
from openpyxl.worksheet.views import (
    SheetViewList,
    SheetView
)
from openpyxl.utils.cell import column_index_from_string
import re
from openpyxl.worksheet.pagebreak import Break

class Coord:
    '''
    代表单元格的坐标  
    '''
    def __init__(self, row:int, col:int)->None:
        '''
        初始化一个新的单元格的坐标
        :param row: int-行坐标，从 1 开始
        :param col: int-列坐标，从 1 开始
        '''
        self.row = row
        self.col = col

    @property
    def colletter(self)->str:
        return get_column_letter(self.col)

class Excell:
    '''
    代表一个单元格
    '''
    def __init__(self, loc:Coord=Coord(1,1), value=None, style=None)->None:
        '''
        初始化一个单元格。可以指定 loc 坐标、value 值、style 样式，如果没有指定，
        坐标默认为Sheet左上角第一个单元格，值和样式默认为空。
        :param loc: Coord-单元格在Sheet中的坐标
        :param value: object-单元格的值
        :param style: object-单元格的样式
        '''
        self.loc = loc
        self.value = value
        self.style = style

class Exblock:
    '''
    代表一个方块域，可以想象成一张黄色便笺纸
    '''
    def __init__(self, martrix:ndarray[Excell], merges:list[str], heights:Series[float], widths:Series[float])->None:
        '''
        初始化方块域
        :param martrix: ndarray[Excell] - 域中所有单元格的集合
        :param merges: list[str] - 域中所有合并单元格的集合
        :param heights: Series[float] - 域中所有行的行高的集合
        :param widths: Series[float] - 域中所有列的列高的集合
        '''
        self.martrix = martrix
        self.merges = merges
        self.heights = heights
        self.widths = widths
        # auto calcuate
        self.number_of_rows = martrix.shape[0]
        self.number_of_cols = martrix.shape[1]

    def offset(self, rows:int, cols:int):
        '''
        Exblock 块偏移操作
        :param rows: int - 向下偏移行数
        :param cols: int - 向右偏移行数 
        '''

        # martrix 偏移操作
        for i in range(0, self.martrix.shape[0]):      # iterrows
            for j in range(0, self.martrix.shape[1]):  # itercols
                
                self.martrix[i][j].loc.row = self.martrix[i][j].loc.row + rows
                self.martrix[i][j].loc.col = self.martrix[i][j].loc.col + cols   
        # merges 偏移操作
        self.merges = [self._convert_merged_range(rng, rows, cols) for rng in self.merges]

        # heights 偏移操作
        self.heights.index = [rows + idx for idx in self.heights.index]  

        # widths 偏移操作
        self.widths.index = [cols + idx for idx in self.widths.index]

    def _convert_merged_range(self, merged_range:str, offset_row:int, offset_col:int)->str:
            '''
            将合并范围字符转换为偏移后的新范围字符
            example: call _convert_merged_range('A1:B2',3,3), return 'D4:E5'
            '''
            loc = []
            rng = merged_range.split(':')
            for i in range(len(rng)):                
                loc.append(
                    self._convert_cell(rng[i], offset_row, offset_col)
                )
            return ':'.join(loc)   

    def _convert_cell(self, cellstr:str, offset_row:int, offset_col:int)->str:
        '''
        将单元格字符地址转换为偏移后新的字符地址
        eg. call _convert_cell('B5', 2, 2)   return 'D7'
        '''
        col, row = re.match(r'([a-zA-Z]+)([0-9]+)', cellstr).groups()
        col = get_column_letter(
            column_index_from_string(col) + offset_col
        )
        row = int(row) + offset_row   
        return f'{col}{row}'


class Exsheet:
    '''
    代表一个与 Excel 的工作表强耦合的方块域
    '''
    NARROW = {
        'top':0.75, 'bottom':0.75,
        'left':0.25, 'right':0.25,
        'header':0.3, 'footer':0.3
    }
    NARROWER ={
        'top':0.4, 'bottom':0.35,
        'left':0.25, 'right':0.25,
        'header':0.3, 'footer':0.3
    }
    WIDE = {
        'top':1, 'bottom':1,
        'left':1, 'right':1,
        'header':0.5, 'footer':0.5     
    }
    NORMAL = {
        'top':0.75, 'bottom':0.75,
        'left':0.7, 'right':0.7,
        'header':0.3, 'footer':0.3
    }

    def __init__(self, 
        absolute_loc:Coord=Coord(1,1), 
        gap_between_blocks_in_a_page:int=0, 
        gap_between_pages:int=0, 
        number_of_blocks_per_page:int=1,
        pattern:Exblock=None)->None:
        '''
        初始化与Sheet强耦合的方块域
        :param absolute_loc: Coord - 绝对位置(在Sheet中可以存在多个方块域，首个方块域在Sheet中的位置就是绝对位置)
        :param gap_between_blocks_in_a_page: int - 处于同一打印页面中的两个方块域相隔的单元行数
        :param gap_between_pages: int - 前一打印页最后一个方块域，与后一打印页第一个方块域之间相隔的单元行数
        :param number_of_blocks_per_page: int - 每张打印页最多可以包含多少个方块域
        :param block: Exblock - 在 sheet 中重复出现的模式块
        '''
        self._Id:int = 0
        self.pattern:Exblock = pattern
        self.block:Exblock = None
        # self._martrix = pattern
        # self._merges = pattern.merges
        self.absolute_loc:Coord = absolute_loc  # 数据块集合的起始位置
        self.gap_between_blocks_in_a_page = gap_between_blocks_in_a_page # 页内块间空行数
        self.gap_between_pages = gap_between_pages # 页间空行数
        self.number_of_blocks_per_page = number_of_blocks_per_page # 每页可容纳块的数量

        # self._orignial_merges = []  # cache valiable
        
    # auto calcuate fields start    
    @property
    def number_of_rows_per_page(self)->int:
        '''
        根据实例的其他相关参数，自动计算每一打印页包含的最大单元行数
        '''
        if (self.pattern is not None) and (self.Id > 0):
            return (
                self.pattern.number_of_rows * self.number_of_blocks_per_page 
                + self.gap_between_blocks_in_a_page * (self.number_of_blocks_per_page - 1)
                + self.gap_between_pages
            ) 
        else:
            return 0
    
    @property
    def sequence_number_in_a_page (self)->int:
        '''
        根据实例的其他相关参数，自动计算当前方块域处于所在打印页的第几块
        '''
        if (self.pattern is not None) & (self.Id > 0):
            return (
                    (self.Id-1) % self.number_of_blocks_per_page 
                    + 1
            )
        else:
            return 0

    @property
    def page_order(self)->int:
        '''
        根据实例的相关参数，自动计算当前方块域处于打印页的第几页
        '''
        if (self.pattern is not None) & (self.Id > 0):
            return int(( (self.Id-1) - (self.sequence_number_in_a_page-1) ) / self.number_of_blocks_per_page) + 1 
        else:
            return 0     

    @property
    def start_loc(self)->Coord:
        '''
        根据实例的相关参数，自动计算当前方块域在Sheet中的起始位置坐标
        '''
        if (self.pattern is not None) & (self.Id > 0):
            
            x = (self.absolute_loc.row 
            + (self.page_order-1) * self.number_of_rows_per_page
            + (self.sequence_number_in_a_page -1) * (self.pattern.number_of_rows + self.gap_between_blocks_in_a_page))
            self._start_loc = Coord(x, self.absolute_loc.col)
            return self._start_loc
    #auto calculate field end

    def read_pattern(self, exfile, idx:int=0)->Exblock:
        '''
        从指定excel文件的指定sheet中，获取模板数据块
        param exfile: excel文件名
        param idx: 第 idx 张表，based-zero
        '''
        wb = load_workbook(exfile)
        ws = wb.worksheets[idx]
        
        # 读取模板块中的单元格
        matrix = []

        for row in range(1, ws.max_row+1):
            cells_in_row = []

            for col in range(1, ws.max_column+1):   

                value = ws.cell(row,col).value

                style = NamedStyle(name=f'r{row}c{col}')
                style.font = copy(ws.cell(row,col).font)
                style.alignment = copy(ws.cell(row,col).alignment)
                style.border = copy(ws.cell(row,col).border)
                style.number_format = copy(ws.cell(row,col).number_format)
      
                cell = Excell(Coord(row,col), value, style)
           
                cells_in_row.append(cell)
         
            matrix.append(cells_in_row)

        matrix = array(matrix)
   
        # 读取模板块中的合并域集
        merges = [rng.coord for rng in ws.merged_cells.ranges]

        # 读取模板块中每一行的行高
        heights = []
        for row in range(1, ws.max_row+1):
            heights.append(
                ws.row_dimensions[row].height
            )
        heights = Series(data=heights, index=range(1,len(heights)+1))

        # 读取模板块中的每一列的列宽
        widths = []
        for col in range(1, ws.max_column+1):
            widths.append(
                ws.column_dimensions[
                    get_column_letter(col)
                ].width
            )
        widths = Series(data=widths, index=range(1,len(widths)+1))
        
        wb.close()

        self.pattern = Exblock(martrix=matrix, merges=merges, heights=heights, widths=widths)
        # self._martrix = deepcopy(matrix)
        # self._merges = merges.copy()
        return self.pattern

    @property
    def Id(self):
        '''
        当前方块域在Sheet中所有方块域从上到下的排列次序
        '''
        return self._Id
    @Id.setter
    def Id(self, value:int)->None:
        '''
        改变当前方块域在Sheet中所有方块域中的排列次序，
        当该属性改变时，方块域中与该属性相关的坐标相应改变
        '''
        if (self.pattern is not None) & (value > 0):
            self._Id = value
            self.block = deepcopy(self.pattern)
            self._reflect_into_block()

    def fill_blank(self, oto_loc:dict, adata:Series, 
                   mtm_loc:dict=None, datas:DataFrame=None)->None:
        '''
        
        向当前实例方块域中的'坑'位，自动填入对应内容
        :param oto_loc: dict - 一对一映射表
        :param adata: Series - 根据一对一映射表填'坑'的数据源
        :param mtm_loc: dict - 一对多映射表
        :param datas: DataFrame - 根据一对多映射表填'坑'的数据源
        ------------------------------------------------------
        当 datas 提供的数据条数多过 mtm_loc 的行范围给定的行数时，
        多出的条目会自动舍弃。

        '''
        if oto_loc is not None and adata is not None:
            self._fill_with_oto(oto_loc, adata)
        if(mtm_loc is not None) & (datas is not None):
            self._fill_with_mtm(mtm_loc, datas)

    def put_in_sheet(self, sheet:Worksheet)->None:
        '''
        把实例的当前方块域放入具体的 Sheet 中
        :param sheet: Worksheet - Excel 工作表
        '''

        # setting the height of all rows in the block 
        for row in self.block.heights.index:           
            sheet.row_dimensions[row].height = self.block.heights[row]

        # setting the width of all columns in the block
        for col in self.block.widths.index:
            sheet.column_dimensions[
                get_column_letter(col)
            ].width = self.block.widths[col]

        # filling the value of all cells in block
        for row in self.block.martrix:
            for cell in row:
                sheet.cell(cell.loc.row, cell.loc.col).value = cell.value
                if cell.style is not None:
                    cell.style.name = f'r{cell.loc.row}c{cell.loc.col}'
                    sheet.cell(cell.loc.row, cell.loc.col).style = cell.style
        
        # merging each range in the block
        for item in self.block.merges:
            sheet.merge_cells(item)

        # to add a row break at the bottom of each page 
        self._add_row_break(sheet)

    def page_layout(self, 
        sheet:Worksheet,  
        margins:dict =NORMAL, 
        scale:float =100, 
        paperSize:str ='0', 
        orientation:str ='portrait', 
        horizontalcentered:bool =True, 
        verticalcentered:bool =True 
    )->None:
        '''
        为给定的sheet进行打印设置
        :param sheet: Worksheet - 要进行打印设置的工作表
        :param margins: dict - 设置打印边距
        :param scale: float - 设置缩放比例
        :param pageSize: str - 设置纸张大小(
            '0'- 默认大小; 
            '1'- Letter；
            '2' - Letter Small;
            '3' - Tabloid;
            '4' - Ledger;
            '5' - Legal;
            '6' - Statement;
            '7' - Executive;
            '8' - A3;
            '9' - A4;
            '10' - A4 Small;
            '11' - A5;)
        :param orientation: - 设置纸张方向(
            'portrait' - 纵向
            'landscape' - 横向)
        :param horizontalcentered: bool - 设置打印内容左右居中(True - 居中; False - 不居中)
        :param verticalcentered: bool - 设置打印内容垂直居中(True - 居中; False - 不居中)
        '''

        self._set_printarea(sheet)

        sheet.page_margins = PageMargins(
            left=margins['left'], 
            right=margins['right'], 
            top=margins['top'], 
            bottom=margins['bottom'],
            header=margins['header'],
            footer=margins['footer']
        )
        sheet.page_setup = PrintPageSetup(
            sheet,
            scale=scale,
            paperSize=paperSize,
            orientation=orientation,
        )            
        sheet.print_options = PrintOptions(
            horizontalCentered=horizontalcentered,
            verticalCentered=verticalcentered
        )

        sheet.views = SheetViewList([SheetView(showGridLines=False, showRowColHeaders=False)])

    def _reflect_into_block(self)->None:
        if (self.block is not None) & (self.Id > 0):
            self.block.offset(self.start_loc.row-self.absolute_loc.row, self.start_loc.col-self.absolute_loc.col)

    def _fill_with_oto(self, oto_loc:dict, adata:Series)->None:
        '''
        以一对一映射模式填空
        '''

        if (oto_loc is not None) & (adata is not None):

            self._clearfill_with_oto(oto_loc, adata)

            for loc in oto_loc:
                if loc is not None:
                    row, col = self._convert_letter_cell_to_number(loc)
                    self.block.martrix[row-1][col-1].value =adata[oto_loc[loc]]

    def _clearfill_with_oto(self, oto_loc:dict, adata:Series)->None:
        '''
        清除一对一模式填空
        '''
        if (oto_loc is not None) & (adata is not None):
       
            for loc in oto_loc:
                if loc is not None:
                    row, col = self._convert_letter_cell_to_number(loc)
                    self.block.martrix[row-1][col-1].value = None

    def _fill_with_mtm(self, mtm_loc:dict, datas:DataFrame)->None:

        if(mtm_loc is not None) & (datas is not None):

            self._clearfill_with_mtm(mtm_loc, datas)
            
            rngkey = [key  for (key, value) in mtm_loc.items() if value == '行范围']
            start_row, end_row = rngkey[0].split(':')    
            count = int(end_row) - int(start_row) + 1         
            # del mtm_loc[rngkey[0]]

            for row in range(0, count):
                for col in [loc for loc in mtm_loc if mtm_loc[loc]!='行范围']:
                    icol = column_index_from_string(col)
                    # print(f'填充值位置: ({row},{icol})   martrix位置：({row+(int(start_row)-1)},{icol-1})')
                    if row < datas.shape[0]:
                        self.block.martrix[row+(int(start_row)-1)][icol-1].value =datas.iloc[row,:][mtm_loc[col]]

    def _clearfill_with_mtm(self, mtm_loc:dict, datas:DataFrame)->None:
        # self._clearfill_with_oto(oto_loc, adata)

        rngkey = [key  for (key, value) in mtm_loc.items() if value == '行范围']
        start_row, end_row = rngkey[0].split(':')    
        
        # del mtm_loc[rngkey[0]]

        for row in range(0, (int(end_row)-int(start_row))+1):          
            for col in [loc for loc in mtm_loc if mtm_loc[loc]!='行范围']:
                icol = column_index_from_string(col)
                
                self.block.martrix[row+(int(start_row)-1)][icol-1].value = None

    def _convert_letter_cell_to_number(self, cellstr:str)->Tuple:
        '''
        将单元格字符地址转换为新的数字地址
        eg. call _convert_letter_cell_to_number('B5')   return (5,2)
        '''
        col, row = re.match(r'([a-zA-Z]+)([0-9]+)', cellstr).groups()
        col = column_index_from_string(col)
        
        row = int(row) 
        return row, col

    def _add_row_break(self, sheet)->None:
        # to add a row break at the bottom of each page 
        if (self.Id  % self.number_of_blocks_per_page) == 0:   
                
            last_row_in_page = self.start_loc.row + self.pattern.number_of_rows + self.gap_between_pages - 1
            
            sheet.row_breaks.append(Break(last_row_in_page))     

    def _set_printarea(self,sheet:Worksheet)->None:
        # to set print area
        print_area = f'{get_column_letter(self.absolute_loc.col)}{self.absolute_loc.row}:{get_column_letter(sheet.max_column)}{sheet.max_row}'
        sheet.print_area = print_area
